/**
 * Created by lenovo on 2017/7/20.
 */
let DBPool = require('../dao/DBConnectPool');
let async = require('async');
let config = require('../../config');
let WXMember = require('./WXMemberController');

module.exports = {
    /**
     * 影片首页
     * 获取广告位
     * 获取筛选信息（初始化）
     */
    showRecommendedMovies: function (req, res) {
        let title = '推荐影片';
        WXMember.getMemberInformation(req,res,function (err, code, result) {
            if(code){
                console.log('WXMoviesController.js(19)'+err);
                res.render('wx-404', {
                    title: title,
                    errorMessage: err
                });
            } else {
                DBPool.getPoolConnection('mtsc', function (err, connection) {
                    if (err) {
                        console.log('WXMoviesController.js(27)'+err);
                        res.render('wx-404', {
                            title: title,
                            errorMessage: '数据库链接错误'
                        });
                        connection.release();
                    } else {
                        async.waterfall([
                            function (callback) {
                                let sql = 'SELECT * FROM movie_banner ' +
                                    ' WHERE banner_status = 1 ' +
                                    ' ORDER BY banner_order ASC';
                                connection.query(sql, null, function (queryErr, rows) {
                                    if (queryErr) {
                                        callback('获取初始化信息（广告内容）发生错误！');
                                    } else {
                                        callback(null, {banners: rows})
                                    }
                                });
                            },
                            function (arg1, callback) {
                                let types = [
                                    {id:132,movie_type:'剧情'},
                                    {id:133,movie_type:'动作'},
                                    {id:134,movie_type:'历史'},
                                    {id:135,movie_type:'战争'},
                                    {id:136,movie_type:'科幻'},
                                    {id:137,movie_type:'奇幻'},
                                    {id:138,movie_type:'冒险'}
                                ];
                                let typesMore = [
                                    {id:140,movie_type:'喜剧'},
                                    {id:139,movie_type:'爱情'},
                                    {id:143,movie_type:'灾难'},
                                    {id:144,movie_type:'动画'},
                                    {id:150,movie_type:'悬疑'},
                                    {id:155,movie_type:'恐怖'},
                                    {id:157,movie_type:'纪录片'},
                                    {id:161,movie_type:'短片'},
                                    {id:142,movie_type:'传记'},
                                    {id:145,movie_type:'歌舞'},
                                    {id:148,movie_type:'武侠'},
                                ];
                                arg1.movie_type = types;
                                arg1.movie_type_more = typesMore;
                                callback(null, arg1);


                                // let sql = 'SELECT * FROM movie_type WHERE isdeleted = 0 AND id > 100 ';
                                // connection.query(sql, null, function (queryErr, rows) {
                                //     if (queryErr) {
                                //         callback('获取初始化信息（影片类型）发生错误！');
                                //     } else {
                                //         let types = [];
                                //         let typesMore = [];
                                //         if (rows.length > 8 ){
                                //             for(let i=0;i<7;i++){
                                //                 types.push(rows[i]);
                                //             }
                                //             types.push({more:'more'});
                                //             for(let i=7;i<rows.length;i++){
                                //                 typesMore.push(rows[i]);
                                //             }
                                //             if (typesMore.length % 4) {
                                //                 let t = 4 - typesMore.length % 4;
                                //                 for (let i = 0; i < t; i++) {
                                //                     typesMore.push('add');
                                //                 }
                                //             }
                                //         } else {
                                //             for(let i=0;i<rows.length;i++){
                                //                 types.push(rows[i]);
                                //             }
                                //             if (types.length % 4) {
                                //                 let t = 4 - types.length % 4;
                                //                 for (let i = 0; i < t; i++) {
                                //                     types.push('add');
                                //                 }
                                //             }
                                //         }
                                //         arg1.movie_type = types;
                                //         arg1.movie_type_more = typesMore;
                                //         callback(null, arg1);
                                //     }
                                // });
                            },
                            function (arg1,callback) {
                                arg1.movie_year = [];
                                arg1.movie_year_more = [];
                                callback(null, arg1);
                                // let sql = 'SELECT * FROM movie_year ';
                                // connection.query(sql, null, function (queryErr, rows) {
                                //     if (queryErr) {
                                //         callback('获取初始化信息（影片年代）发生错误！');
                                //     } else {
                                //         let years = [];
                                //         let yearsMore = [];
                                //         if (rows.length > 8 ){
                                //             for(let i=0;i<7;i++){
                                //                 years.push(rows[i]);
                                //             }
                                //             years.push({more:'more'});
                                //             for(let i=7;i<rows.length;i++){
                                //                 yearsMore.push(rows[i]);
                                //             }
                                //             if (yearsMore.length % 4) {
                                //                 let t = 4 - yearsMore.length % 4;
                                //                 for (let i = 0; i < t; i++) {
                                //                     yearsMore.push('add');
                                //                 }
                                //             }
                                //         } else {
                                //             for(let i=0;i<rows.length;i++){
                                //                 years.push(rows[i]);
                                //             }
                                //             if (years.length % 4) {
                                //                 let t = 4 - years.length % 4;
                                //                 for (let i = 0; i < t; i++) {
                                //                     years.push('add');
                                //                 }
                                //             }
                                //         }
                                //         arg1.movie_year = years;
                                //         arg1.movie_year_more = yearsMore;
                                //         callback(null, arg1)
                                //     }
                                // });

                            },
                            function (arg1, callback) {
                                let countries = [
                                    {id:251,movie_country:'中国大陆'},
                                    {id:253,movie_country:'香港'},
                                    {id:250,movie_country:'台湾'},
                                    {id:248,movie_country:'美国'},
                                    {id:249,movie_country:'英国'},
                                    {id:254,movie_country:'法国'},
                                    {id:255,movie_country:'日本'}
                                ];
                                let countriesMore = [
                                    {id:259,movie_country:'韩国'},
                                    {id:261,movie_country:'意大利'},
                                    {id:258,movie_country:'德国'},
                                    {id:262,movie_country:'俄罗斯'},
                                    {id:271,movie_country:'西班牙'},
                                    {id:276,movie_country:'印度'},
                                    {id:273,movie_country:'捷克'},
                                    {id:275,movie_country:'澳大利亚'},
                                    {id:309,movie_country:'伊朗'},
                                    {id:279,movie_country:'巴西'},
                                    {id:277,movie_country:'泰国'},
                                ];
                                arg1.movie_country = countries;
                                arg1.movie_country_more = countriesMore;
                                callback(null, arg1);

                                // let sql = 'SELECT * FROM movie_country WHERE isdeleted = 0 AND id > 100 ';
                                // connection.query(sql, null, function (queryErr, rows) {
                                //     if (queryErr) {
                                //         callback('获取初始化信息（影片国家）发生错误！');
                                //     } else {
                                //         let countries = [];
                                //         let countriesMore = [];
                                //         if (rows.length > 8 ){
                                //             for(let i=0;i<7;i++){
                                //                 countries.push(rows[i]);
                                //             }
                                //             countries.push({more:'more'});
                                //             for(let i=7;i<rows.length;i++){
                                //                 countriesMore.push(rows[i]);
                                //             }
                                //             if (countriesMore.length % 4) {
                                //                 let t = 4 - countriesMore.length % 4;
                                //                 for (let i = 0; i < t; i++) {
                                //                     countriesMore.push('add');
                                //                 }
                                //             }
                                //         } else {
                                //             for(let i=0;i<rows.length;i++){
                                //                 countries.push(rows[i]);
                                //             }
                                //             if (countries.length % 4) {
                                //                 let t = 4 - countries.length % 4;
                                //                 for (let i = 0; i < t; i++) {
                                //                     countries.push('add');
                                //                 }
                                //             }
                                //         }
                                //         arg1.movie_country = countries;
                                //         arg1.movie_country_more = countriesMore;
                                //         callback(null, arg1);
                                //     }
                                // });

                            },
                        ], function (err, arg1) {
                            if (err) {
                                console.log('WXMoviesController.js(168)'+err);
                                res.render('wx-404', {
                                    title: title,
                                    errorMessage: err
                                });
                                connection.release();
                            } else {
                                WXMember.queryConfig(req, function (data) {
                                    res.render('wx-recommendedmovies', {
                                        title: title,
                                        banners: arg1.banners,
                                        movie_type: arg1.movie_type,
                                        movie_year: arg1.movie_year,
                                        movie_country: arg1.movie_country,
                                        movie_type_more: arg1.movie_type_more,
                                        movie_year_more: arg1.movie_year_more,
                                        movie_country_more: arg1.movie_country_more,

                                        sdkKey: config.amap.sdkKey,
                                        open_id: result.openid,
                                        appId: config.app.appid,
                                        sign_pkg: data.jsapi_ticket,
                                        timestamp: data.timestamp,
                                        nonceStr: data.nonceStr,
                                        signature: data.signature,
                                        ak: config.bmap.AK,
                                    });
                                    connection.release();
                                });
                            }
                        });
                    }
                });
            }
        });
    },

    /**
     * 获取筛选信息（初始化）
     * @param req
     * @param res
     */
    getRecommendedMovies: function (req, res) {
        WXMember.checkPhoneNumber(req,res,function (err, code, result) {
            let limit = 10;
            let page = req.body.page;
            let memberId = null;
            if (result){
                memberId = result.member_id;
            }
            DBPool.getPoolConnection('mtsc', function (err, connection) {
                if (err) {
                    res.json({errorMessage: '数据库链接错误'});
                    connection.release();
                    return;
                }
                async.waterfall(
                    [
                        function (callback) {
                            let sql = 'SELECT a.album_id,a.album_name,a.album_comment,a.wx_poster,a.updatetime,c.collection_id ' +
                                ' FROM movie_album as a ' +
                                ' LEFT JOIN member_album_collection as c ON c.album_id = a.album_id AND c.member_id = ? ' +
                                ' WHERE a.album_status = ? ' +
                                ' ORDER BY a.updatetime DESC ' +
                                ' LIMIT ?,?';
                            connection.query(sql, [memberId,1, page * limit, limit], function (queryErr, rows) {
                                if (queryErr) {
                                    callback(queryErr);
                                } else {
                                    let isLast = 0;
                                    if (rows.length < limit) {
                                        isLast = 1;
                                    }
                                    callback(null, {albums: rows, isLast: isLast});
                                }
                            });
                        },
                        function (arg1, callback) {
                            if (arg1.albums.length > 0){
                                let sql = 'SELECT COUNT(mid) as movie_count ' +
                                    ' FROM movie_album_list ' +
                                    ' WHERE album_id = ? ';

                                for (let i = 0; i < arg1.albums.length; i++) {
                                    connection.query(sql, [arg1.albums[i].album_id], function (queryErr, rows) {
                                        if (queryErr) {
                                            callback('获取专辑影片列表失败！');
                                        } else {
                                            arg1.albums[i].movie_count = rows[0].movie_count;
                                            if (i === arg1.albums.length - 1) {
                                                callback(null, arg1)
                                            }
                                        }
                                    });
                                }
                            } else {
                                callback(null, arg1)
                            }

                        },
                        function (arg1, callback) {
                            if (arg1.albums.length > 0){
                                let sql = 'SELECT al.album_id,m.*,c.collection_id ' +
                                    ' FROM movie_album_list as al ' +
                                    ' INNER JOIN movie as m ON m.mid = al.mid ' +
                                    ' LEFT JOIN member_movie_collection as c ON c.mid = m.mid AND c.member_id = ?' +
                                    ' WHERE al.album_id = ? ' +
                                    ' ORDER BY al.show_pos DESC ' +
                                    ' LIMIT 0,6 ';

                                for (let i = 0; i < arg1.albums.length; i++) {
                                    connection.query(sql, [memberId,arg1.albums[i].album_id], function (queryErr, rows) {
                                        if (queryErr) {
                                            callback('获取专辑影片列表失败！');
                                        } else {
                                            arg1.albums[i].movies = rows;
                                            if (i === arg1.albums.length - 1) {
                                                callback(null, arg1)
                                            }
                                        }
                                    });
                                }
                            } else {
                                callback(null, arg1)
                            }
                        },
                    ], function (err, result) {
                        if (err) {
                            res.json({
                                errorMessage: err
                            });
                        } else {
                            res.json(result);
                        }
                        connection.release();
                    });
            });
        });
    },

    /**
     * 影片列表页
     * 专辑/筛选后
     */
    showFiltedMovies: function (req, res) {

        let searchMode = parseInt(req.query.mode);
        let parameter = req.query.parameter;
        let title = '影片一览';

        switch (searchMode) {
            case 0:
                //专辑影片列表
                DBPool.getPoolConnection('mtsc', function (err, connection) {
                    if (err) {
                        res.render('wx-404', {
                            title: title,
                            errorMessage: '数据库链接错误'
                        });
                        connection.release();
                        return;
                    }

                    let sql = 'SELECT COUNT(l.album_id) as cnt,a.album_id,a.updatetime,a.album_name,a.album_comment,a.wx_poster ' +
                        ' FROM movie_album_list as l ' +
                        ' INNER JOIN movie_album as a ON l.album_id = a.album_id ' +
                        ' WHERE a.album_id = ? ' +
                        ' GROUP BY l.album_id ';
                    connection.query(sql, [parameter], function (queryErr, row) {
                        if (queryErr) {
                            res.render('wx-404', {
                                title: title,
                                errorMessage: '数据库链接错误'
                            });
                            connection.release();
                        } else {
                            res.render('wx-filtedmovies', {
                                title: title,
                                poster: config.cdn.imgs + '/ALBUM/' + row[0].album_id +'/'+ row[0].wx_poster,
                                cnt: row[0].cnt,
                                name: row[0].album_name,
                                comment: row[0].album_comment,
                                time: row[0].updatetime
                            });
                            connection.release();
                        }
                    });
                });
                break;
            case 1:
                //按分类筛选
                DBPool.getPoolConnection('mtsc', function (err, connection) {
                    if (err) {
                        res.render('wx-404', {
                            title: title,
                            errorMessage: '数据库链接错误'
                        });
                        connection.release();
                        return;
                    }

                    let sql = 'SELECT m.mid,m.poster,mt.movie_type,mt.id as tid ' +
                        ' FROM movie as m ' +
                        ' INNER JOIN movietypes as t ON t.movie_id = m.mid ' +
                        ' INNER JOIN movie_type as mt ON mt.id = t.type_id ' +
                        ' WHERE t.type_id = ? ' +
                        ' ORDER BY m.publishdate DESC ' +
                        ' LIMIT 0,1';
                    connection.query(sql, [parameter], function (queryErr, row) {
                        if (queryErr) {
                            res.render('wx-404', {
                                title: title,
                                errorMessage: '数据库链接错误'
                            });
                            connection.release();
                        } else if (row.length === 0) {
                            res.render('wx-404', {
                                title: title,
                                errorMessage: '什么都没找到'
                            });
                            connection.release();
                        } else {
                            res.render('wx-filtedmovies', {
                                title: title,
                                mid: row[0].mid,
                                //poster: config.cdn.imgs + '/MOVIE/' + row[0].mid + '/POSTER/' + row[0].poster,
                                poster: config.cdn.prefix + 'img/type/' + row[0].tid + '.jpg',
                                cnt: null,
                                // name: row[0].movie_type,
                                name: null,
                                comment: null,
                                time: null
                            });
                            connection.release();
                        }
                    });
                });
                break;
            case 2:
                //按国家筛选
                DBPool.getPoolConnection('mtsc', function (err, connection) {
                    if (err) {
                        res.render('wx-404', {
                            title: title,
                            errorMessage: '数据库链接错误'
                        });
                        connection.release();
                        return;
                    }

                    let sql = 'SELECT m.mid,m.poster,mc.movie_country,mc.id as tid ' +
                        ' FROM movie as m ' +
                        ' INNER JOIN moviecountries as c ON c.movie_id = m.mid ' +
                        ' INNER JOIN movie_country as mc ON mc.id = c.country_id ' +
                        ' WHERE c.country_id = ? ' +
                        ' ORDER BY m.publishdate DESC ' +
                        ' LIMIT 0,1';
                    connection.query(sql, [parameter], function (queryErr, row) {
                        if (queryErr) {
                            res.render('wx-404', {
                                title: title,
                                errorMessage: '数据库链接错误'
                            });
                            connection.release();
                        } else if (row.length === 0) {
                            res.render('wx-404', {
                                title: title,
                                errorMessage: '什么都没找到'
                            });
                            connection.release();
                        } else {
                            res.render('wx-filtedmovies', {
                                title: title,
                                mid:row[0].mid,
                                // poster: config.cdn.imgs + '/MOVIE/' + row[0].mid + '/POSTER/' + row[0].poster,
                                poster: config.cdn.prefix + 'img/country/' + row[0].tid + '.jpg',
                                cnt: null,
                                // name: row[0].movie_country,
                                name: null,
                                comment: null,
                                time: null
                            });
                            connection.release();
                        }
                    });
                });
                break;
            case 3:
                //按年份筛选
                DBPool.getPoolConnection('mtsc', function (err, connection) {
                    if (err) {
                        res.render('wx-404', {
                            title: title,
                            errorMessage: '数据库链接错误'
                        });
                        connection.release();
                        return;
                    }

                    let sql = 'SELECT m.mid,m.poster,my.movie_year ' +
                        ' FROM movie as m ' +
                        ' INNER JOIN movieyears as y ON y.movie_id = m.mid ' +
                        ' INNER JOIN movie_year as my ON my.id = y.year_id ' +
                        ' WHERE y.year_id = ? ' +
                        ' ORDER BY m.publishdate DESC ' +
                        ' LIMIT 0,1';
                    connection.query(sql, [parameter], function (queryErr, row) {
                        if (queryErr) {
                            res.render('wx-404', {
                                title: title,
                                errorMessage: '数据库链接错误'
                            });
                            connection.release();
                        } else if (row.length === 0) {
                            res.render('wx-404', {
                                title: title,
                                errorMessage: '什么都没找到'
                            });
                            connection.release();
                        } else {
                            res.render('wx-filtedmovies', {
                                title: title,
                                mid:row[0].mid,
                                poster: config.cdn.imgs + '/MOVIE/' + row[0].mid + '/POSTER/' + row[0].poster,
                                cnt: null,
                                name: row[0].movie_year,
                                comment: null,
                                time: null
                            });
                            connection.release();
                        }
                    });
                });
                break;
            case 4:
                DBPool.getPoolConnection('mtsc', function (err, connection) {
                    if (err) {
                        res.render('wx-404', {
                            title: title,
                            errorMessage: '数据库链接错误'
                        });
                        connection.release();
                        return;
                    }

                    let sql = 'SELECT DISTINCT m.mid,m.poster,c1.cinema_name ' +
                        ' FROM movie as m  ' +
                        ' INNER JOIN movie_package_detail as p ON p.mid = m.mid ' +
                        ' INNER JOIN contract as c ON c.contract_code = p.contract_code ' +
                        ' INNER JOIN cinema as c1 ON c1.cinema_code = c.cinema_code ' +
                        ' WHERE c.cinema_code = ? ' +
                        ' AND c.`status` = 1 ' +
                        ' AND c.review_status = 2 ' +
                        ' AND c.isdeleted = 0 ' +
                        ' ORDER BY m.publishdate DESC ' +
                        ' LIMIT 0,1';
                    connection.query(sql, [parameter], function (queryErr, row) {
                        if (queryErr) {
                            res.render('wx-404', {
                                title: title,
                                errorMessage: '数据库链接错误'
                            });
                            connection.release();
                        } else if (row.length === 0) {
                            res.render('wx-404', {
                                title: title,
                                errorMessage: '什么都没找到'
                            });
                            connection.release();
                        } else {
                            res.render('wx-filtedmovies', {
                                title: title,
                                mid:row[0].mid,
                                poster: config.cdn.imgs + '/MOVIE/' + row[0].mid + '/POSTER/' + row[0].poster,
                                cnt: null,
                                name: row[0].cinema_name,
                                comment: null,
                                time: null
                            });
                            connection.release();
                        }
                    });
                });
                break;
            default:
                res.render('wx-404', {
                    title: title,
                    errorMessage: '参数错误'
                });
                break
        }
    },
    /**
     * 获取影片列表
     * @param req
     * @param res
     */
    getFiltedMovies: function (req, res) {
        let searchMode = parseInt(req.body.mode);
        let parameter = req.body.parameter;
        let page = parseInt(req.body.page);
        let limit = 10;

        switch (searchMode) {
            case 0:
                //专辑影片列表
                DBPool.getPoolConnection('mtsc', function (err, connection) {
                    if (err) {
                        res.json({
                            errorMessage: '数据库链接错误'
                        });
                        connection.release();
                        return;
                    }
                    let sql = 'SELECT m.*,DATE_FORMAT(m.publishdate,"%Y-%m-%d") as publish ' +
                        ' FROM movie as m ' +
                        ' INNER JOIN movie_album_list as l ON l.mid = m.mid ' +
                        ' WHERE l.album_id = ? ' +
                        ' ORDER BY m.publishdate DESC ' +
                        ' LIMIT ?,?';
                    connection.query(sql, [parameter, page * limit, limit], function (queryErr, rows) {
                        if (queryErr) {
                            res.json({
                                errorMessage: '数据库链接错误'
                            });
                            connection.release();
                        } else {
                            let isLast = 0;
                            if (rows.length < limit) {
                                isLast = 1;
                            }
                            res.json({
                                movies: rows,
                                isLast: isLast
                            });
                            connection.release();
                        }
                    });
                });
                break;
            case 1:
                //按分类筛选
                DBPool.getPoolConnection('mtsc', function (err, connection) {
                    if (err) {
                        res.render({
                            errorMessage: '数据库链接错误'
                        });
                        connection.release();
                        return;
                    }

                    let sql = 'SELECT m.*,DATE_FORMAT(m.publishdate,"%Y-%m-%d") as publish ' +
                        ' FROM movie as m ' +
                        ' INNER JOIN movietypes as t ON t.movie_id = m.mid ' +
                        ' WHERE t.type_id = ? ' +
                        ' ORDER BY m.publishdate DESC ' +
                        ' LIMIT ?,?';
                    connection.query(sql, [parameter, page * limit, limit], function (queryErr, rows) {
                        if (queryErr) {
                            res.json({
                                errorMessage: '数据库链接错误'
                            });
                            connection.release();
                        } else {
                            let isLast = 0;
                            if (rows.length < limit) {
                                isLast = 1;
                            }
                            res.json({
                                movies: rows,
                                isLast: isLast
                            });
                            connection.release();
                        }
                    });
                });
                break;
            case 2:
                //按国家筛选
                DBPool.getPoolConnection('mtsc', function (err, connection) {
                    if (err) {
                        res.json({
                            errorMessage: '数据库链接错误'
                        });
                        connection.release();
                        return;
                    }

                    let sql = 'SELECT m.*,DATE_FORMAT(m.publishdate,"%Y-%m-%d") as publish ' +
                        ' FROM movie as m ' +
                        ' INNER JOIN moviecountries as c ON c.movie_id = m.mid ' +
                        ' WHERE c.country_id = ? ' +
                        ' ORDER BY m.publishdate DESC ' +
                        ' LIMIT ?,?';
                    connection.query(sql, [parameter, page * limit, limit], function (queryErr, rows) {
                        if (queryErr) {
                            res.json({
                                errorMessage: '数据库链接错误'
                            });
                            connection.release();
                        } else {
                            let isLast = 0;
                            if (rows.length < limit) {
                                isLast = 1;
                            }
                            res.json({
                                movies: rows,
                                isLast: isLast
                            });
                            connection.release();
                        }
                    });
                });
                break;
            case 3:
                //按年份筛选
                DBPool.getPoolConnection('mtsc', function (err, connection) {
                    if (err) {
                        res.json({
                            errorMessage: '数据库链接错误'
                        });
                        connection.release();
                        return;
                    }

                    let sql = 'SELECT m.*,DATE_FORMAT(m.publishdate,"%Y-%m-%d") as publish ' +
                        ' FROM movie as m ' +
                        ' INNER JOIN movieyears as y ON y.movie_id = m.mid ' +
                        ' WHERE y.year_id = ? ' +
                        ' ORDER BY m.publishdate DESC ' +
                        ' LIMIT ?,?';
                    connection.query(sql, [parameter, page * limit, limit], function (queryErr, rows) {
                        if (queryErr) {
                            res.json({
                                errorMessage: '数据库链接错误'
                            });
                            connection.release();
                        } else {
                            let isLast = 0;
                            if (rows.length < limit) {
                                isLast = 1;
                            }
                            res.json({
                                movies: rows,
                                isLast: isLast
                            });
                            connection.release();
                        }
                    });
                });
                break;
            case 4:
                DBPool.getPoolConnection('mtsc', function (err, connection) {
                    if (err) {
                        res.json({
                            errorMessage: '数据库链接错误'
                        });
                        connection.release();
                        return;
                    }

                    let sql = 'SELECT DISTINCT m.*,DATE_FORMAT(m.publishdate,"%Y-%m-%d") as publish ' +
                        ' FROM movie as m  ' +
                        ' INNER JOIN movie_package_detail as p ON p.mid = m.mid ' +
                        ' INNER JOIN contract as c ON c.contract_code = p.contract_code ' +
                        ' WHERE c.cinema_code = ? ' +
                        ' AND c.`status` = 1 ' +
                        ' AND c.review_status = 2 ' +
                        ' AND c.isdeleted = 0 ' +
                        ' ORDER BY m.publishdate DESC ' +
                        ' LIMIT ?,?';

                    connection.query(sql, [parameter, page * limit, limit], function (queryErr, rows) {
                        if (queryErr) {
                            res.json({
                                errorMessage: '数据库链接错误'
                            });
                            connection.release();
                        } else {
                            let isLast = 0;
                            if (rows.length < limit) {
                                isLast = 1;
                            }
                            res.json({
                                movies: rows,
                                isLast: isLast
                            });
                            connection.release();
                        }
                    });
                });
                break;
            default:
                res.json({
                    errorMessage: '参数错误'
                });
                break
        }
    },
    /**
     * 搜索影片
     * @param req
     * @param res
     */
    getSearchedMovies: function (req, res) {
        let searchKey =  req.body.searchKey;
        searchKey= searchKey.replace(/([\-\_\,\!\|\~\`\(\)\#\$\%\^\&\*\{\}\:\;\"\L\<\>\?])/g, '\\$1');
        searchKey = '%' + searchKey + '%';

        DBPool.getPoolConnection('mtsc', function (err, connection) {
            if (err) {
                res.json({
                    errorMessage: '数据库链接错误'
                });
                connection.release();
                return;
            }
            let sql = 'SELECT *,DATE_FORMAT(publishdate,"%Y-%m-%d") as publish ' +
                ' FROM movie ' +
                ' WHERE movie_name LIKE ? ' +
                ' OR movie_ename LIKE ? ' +
                ' ORDER BY publishdate DESC ';
            connection.query(sql, [searchKey, searchKey], function (queryErr, rows) {
                if (queryErr) {
                    res.json({errorMessage: '数据库链接错误'});
                    connection.release();
                } else {
                    res.json({
                        movies: rows,
                    });
                    connection.release();
                }
            });
        });
    },

    /**
     * 影片详情页
     */
    showMovieHome: function (req, res) {

        let mid = req.query.mid;
        let from = req.query.from;
        let title = '影片介绍';
        let openId = null;
        if (req.signedCookies.userinfo && req.signedCookies.userinfo.openid ){
            openId = req.signedCookies.userinfo.openid;
        }

        DBPool.getPoolConnection('mtsc', function (err, connection) {
            if (err) {
                res.render('wx-404', {
                    title: title,
                    errorMessage: '数据库链接错误'
                });
                connection.release();
                return;
            }
            async.waterfall([
                function (callback) {
                    let sql = ' SELECT *, DATE_FORMAT(publishdate,"%Y-%m-%d") as publish ' +
                        ' FROM movie ' +
                        ' WHERE mid = ? ';
                    connection.query(sql, [mid], function (queryErr, row) {
                        if (queryErr) {
                            callback('获取影片信息失败！');
                        } else {
                            if (row.length === 0) {
                                callback('没有找到相关影片！');
                            } else {
                                console.log('WXMoviesController.js:'+row[0].score);
                                callback(null, row[0])
                            }
                        }
                    });

                    // let sql = 'SELECT COUNT(c2.cinema_code) as cinemacount,' +
                    //     ' m.mid,m.poster,m.movie_name,m.country,m.category,m.filmlength, ' +
                    //     ' DATE_FORMAT(m.publishdate,"%Y-%m-%d") as publish,m.score,m.brief ' +
                    //     ' FROM movie as m ' +
                    //     ' INNER JOIN movie_package_detail as mpd ON mpd.mid = m.mid ' +
                    //     ' INNER JOIN contract as c ON c.contract_code = mpd.contract_code ' +
                    //     ' INNER JOIN cinema as c2 ON c.cinema_code = c2.cinema_code ' +
                    //     ' WHERE m.mid = ? ' +
                    //     ' AND c.`status` = 1 ' +
                    //     ' AND c.review_status = 2 ' +
                    //     ' AND c.isdeleted = 0 ' +
                    //     ' AND c2.`biz_status` = 2 ' +
                    //     ' AND c2.`api_key` IS NOT NULL ' +
                    //     ' GROUP BY m.mid ';
                    // connection.query(sql, [mid], function (queryErr, row) {
                    //     if (queryErr) {
                    //         callback('获取影片信息失败！');
                    //     } else {
                    //         if (row.length === 0) {
                    //             callback('没有找到相关影片！');
                    //         } else {
                    //             callback(null, {
                    //                 title: title,
                    //                 mid: row[0].mid,
                    //                 poster: row[0].poster,
                    //                 movie_name: row[0].movie_name,
                    //                 country: row[0].country,
                    //                 publish: row[0].publish,
                    //                 score: row[0].score,
                    //                 brief: row[0].brief,
                    //                 cinemacount: row[0].cinemacount,
                    //                 category: row[0].category,
                    //                 filmlength: row[0].filmlength
                    //             })
                    //         }
                    //     }
                    // });
                },
                function (arg1, callback) {
                    if (from){
                        arg1.cinemacount = null;
                        callback(null, arg1);
                    } else {
                        let sql = 'SELECT COUNT(DISTINCT c.cinema_code) as cinemacount  ' +
                            ' FROM cinema as c  ' +
                            ' INNER JOIN contract as c1 ON c1.cinema_code = c.cinema_code ' +
                            ' INNER JOIN movie_package_detail as p ON p.contract_code = c1.contract_code ' +
                            ' WHERE p.mid = ? ' +
                            ' AND c.`biz_status` = 2 ' +
                            ' AND c1.`status` = 1 ' +
                            ' AND c1.review_status = 2 ' +
                            ' AND (c.api_key IS NOT NULL AND c.api_key <> "") ' +
                            ' AND c1.isdeleted = 0 ';

                        connection.query(sql, [mid], function (queryErr, row) {
                            if (queryErr) {
                                callback('获取可预约影院失败！');
                            } else {
                                if (row.length === 0) {
                                    console.log('yql debug', '1');
                                    arg1.cinemacount = null;
                                } else {
                                    console.log('yql debug', '2');
                                    arg1.cinemacount = row[0].cinemacount;
                                }
                                callback(null, arg1)
                            }
                        });
                    }
                },
                function (arg1, callback) {
                    if (!openId) {
                        arg1.collection_id = null;
                        callback(null, arg1);
                    } else {
                        let sql = 'SELECT c.* ' +
                            ' FROM member_movie_collection as c ' +
                            ' INNER JOIN member_user as u ON u.member_id = c.member_id ' +
                            ' WHERE u.user_status = 0 ' +
                            ' AND u.openid = ? ' +
                            ' AND c.mid = ? ' +
                            ' AND u.phone_number <> 0 ';
                        connection.query(sql, [openId, mid], function (queryErr, row) {
                            if (queryErr) {
                                callback('获取收藏信息失败！');
                            } else {
                                if (row.length === 0) {
                                    console.log('yql debug', '1');
                                    arg1.collection_id = null;
                                } else {
                                    console.log('yql debug', '2');
                                    arg1.collection_id = row[0].collection_id;
                                }
                                callback(null, arg1)
                            }
                        });
                    }
                },
            ], function (err, result) {
                if (err) {
                    res.render('wx-404', {
                        title: title,
                        errorMessage: '数据库链接错误！'
                    });
                }else {
                    result.title = title;
                    res.render('wx-moviehome', result);
                }
                connection.release();
            });
        });
    },

    getCinemasByMid: function (req, res) {
        let mid = req.body.mid;
        let lat = req.body.lat;
        let lng = req.body.lng;

        DBPool.getPoolConnection('mtsc', function (err, connection) {
            if (err) {
                res.json({errorMessage: '数据库链接错误'});
                connection.release();
                return;
            }

            let sql = 'SELECT DISTINCT c.*,(c.cinema_lat - ?) * (c.cinema_lat - ?) + (c.`cinema_long` - ?) * (c.`cinema_long` - ?) AS distance ' +
                ' FROM cinema as c  ' +
                ' INNER JOIN contract as c1 ON c1.cinema_code = c.cinema_code ' +
                ' INNER JOIN movie_package_detail as p ON p.contract_code = c1.contract_code ' +
                ' WHERE p.mid = ? ' +
                ' AND c.`biz_status` = 2' +
                ' AND c1.`status` = 1 ' +
                ' AND c1.review_status = 2 ' +
                ' AND (c.api_key IS NOT NULL AND c.api_key <> "") ' +
                ' AND c1.isdeleted = 0 ' +
                ' ORDER BY distance ';

            connection.query(sql, [lat,lat,lng,lng,mid], function (queryErr, rows) {
                if (queryErr) {
                    res.json({errorMessage: '数据库链接错误'});
                } else {
                    if (rows.length === 0) {
                        res.json({errorMessage: '没有找到正在上映的影院'});
                    } else {
                        res.json({
                            rows: rows
                        });
                    }
                }
                connection.release();
            });
        });
    }
};
